package dml;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import pojo.Transaction;

import connection.ConnectionToDB;

public class DMLFunction {
	ConnectionToDB connectionToDB;
	
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    Connection conn;
    
    public DMLFunction() {
    	conn = new ConnectionToDB().getConnection();
	}
    
    private int SelectMax(Connection c) throws SQLException{
    	statement = conn.createStatement();
    	resultSet = statement.executeQuery("SELECT MAX(`transaction_id`) AS transaction_id FROM transaction_;");
    	int max = 0;
    	if (resultSet.next()){

    		max  = resultSet.getInt("transaction_id");
       }

    	return max;
    }
    
 
	public Boolean InsertTransaction(String type, int amount, String reason, String userId) throws SQLException{
	    preparedStatement = conn.prepareStatement("INSERT INTO transaction_(transaction_id,transaction_type,amount,transaction_reason,user_id) values(?,?,?,?,?);");
	    preparedStatement.setInt(1, SelectMax(conn)+1);
	    preparedStatement.setString(2, type);
	    preparedStatement.setInt(3, amount);
	    preparedStatement.setString(4, reason);
	    preparedStatement.setString(5, userId);
	    preparedStatement.executeUpdate();
	    return true;
	}
	
	public List<Transaction> getTransactionHistory(String userId) throws SQLException{
		// statements allow to issue SQL queries to the database
		preparedStatement = conn.prepareStatement("SELECT * FROM transaction_ WHERE user_id = ?;");
	    preparedStatement.setString(1, userId);
	    // resultSet gets the result of the SQL query
	    resultSet = preparedStatement.executeQuery();
	    // resultSet is initialised before the first data set
	  
	    List<Transaction> listTransaction = new ArrayList<Transaction>();
	    
        while (resultSet.next()) {
        	// it is possible to get the columns via name
        	// also possible to get the columns via the column number
        	// which starts at 1
        	// e.g., resultSet.getSTring(2);
        	Transaction transaction = new Transaction();
        	transaction.setTransactionType(resultSet.getString("transaction_type"));
        	transaction.setTransactionReason(resultSet.getString("transaction_reason"));
        	transaction.setAmount(resultSet.getInt("amount"));
        	transaction.setUserId(resultSet.getString("user_id"));
        	listTransaction.add(transaction);
        }

        /*String[] strarray = new String[listTransaction.size()];
        strarray = listTransaction.toArray(strarray); convert from List to String*/
       
        return listTransaction;
	}
}
